package io.mft.commons.db.metadata;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import io.mft.commons.db.mapper.TableDbMapper;
import io.mft.commons.db.source.DbProvider;
import lombok.Getter;
/**
 * 数据表约束管理器
 * @说明：TODO
 * @类型名称：TableConstraintMetadata
 * @创建者: 胡常建  
 * @创建时间: 2020年10月14日 下午5:59:54
 * @修改者: 胡常建  
 * @修改时间: 2020年10月14日 下午5:59:54
 */
@Component
public class TableConstraintMetadata {

	static Map<String, Map<String, DatasourceTableWrapper>> cache=new HashMap<>();
	
	private static final Logger log = LoggerFactory.getLogger(TableConstraintMetadata.class);
	public static void startInitToMemory() {
		   initToMemory();
			Executors.newSingleThreadExecutor().execute(new Runnable() {
				
				@Override
				public void run() {
					try {
						 while (true) {
							 try {
								 TimeUnit.SECONDS.sleep(5);
								 initToMemory();
							} catch (Exception e) {
								log.error("",e);
							}
						}
					} catch (Exception e) {
						log.error("",e);
					}
					
				}
			});
		}
	
	public static DatasourceTableWrapper getDatasourceTableWrapper(String datasourceId,String tableName) {
		Map<String, DatasourceTableWrapper>map=cache.get(datasourceId);
		if (map!=null) {
			return map.get(tableName);
		}else {
			return null;
		}
	}
	private static void initToMemory() {
		try {
			Map<String, Map<String, DatasourceTableWrapper>> map=new HashMap<>();
			List<String> datasourceIds=DbProvider.getInstance().getDatasourceIdList();
			for (String datasourceId : datasourceIds) {
				List<Table> tables=loadTable(datasourceId);
				Map<String, Table> tableMap=new HashMap<>();
				tables.forEach(t->{
					tableMap.put(t.getTableName(), t);
				});
				Map<String, DatasourceTableWrapper> datasourceTableWrapperMap=map.get(datasourceId);
				if (datasourceTableWrapperMap==null) {
					datasourceTableWrapperMap=new HashMap<>();
					map.put(datasourceId, datasourceTableWrapperMap);
				}
				List<TableConstraint> tableConstraints=loadTableConstraint(datasourceId);
				for (TableConstraint tableConstraint : tableConstraints) {
					TableConstraintWrapper tableConstraintWrapper=new TableConstraintWrapper();
					tableConstraintWrapper.constraint=tableConstraint;
					if (!tableMap.containsKey(tableConstraint.getTableName())) {
						continue;
					}
					List<TableIndexColumn> tableIndexColumns=loadTableIndexColumn(datasourceId, tableConstraint.getTableName(),tableConstraint.getConstraintName());
					tableConstraintWrapper.tableIndexColumns.addAll(tableIndexColumns);
					DatasourceTableWrapper datasourceTableWrapper=datasourceTableWrapperMap.get(tableConstraint.getTableName());
					if (datasourceTableWrapper==null) {
						datasourceTableWrapper=new DatasourceTableWrapper();
						datasourceTableWrapper.datasourceId=datasourceId;
						datasourceTableWrapper.tableName=tableConstraint.getTableName();
						datasourceTableWrapperMap.put(tableConstraint.getTableName(), datasourceTableWrapper);
					}
					datasourceTableWrapper.tableConstraintWrappers.add(tableConstraintWrapper);
				}
			}
			cache=map;
		} catch (Exception e) {
			log.error("",e);
		}
	}
	@Getter
	public static class TableConstraintWrapper{
		TableConstraint constraint;
		final List<TableIndexColumn> tableIndexColumns=new ArrayList<>();
	}
	public enum ConstraintType{
		UNIQUE,PRIMARYKEY,FOREIGNKEY
	}
	@Getter
	public static class DatasourceTableWrapper{
		String datasourceId;
		String tableName;
		final List<TableConstraintWrapper> tableConstraintWrappers=new ArrayList<>();
	}
	
	private static Entity rsToEntity(ResultSet rs, ResultSetMetaData metaData) throws SQLException
	{
		Entity entity = new Entity();
		int count = metaData.getColumnCount();
		for (int i = 0; i < count; i++) {
			String fieldName = metaData.getColumnLabel(i + 1);
			Object value = rs.getObject(fieldName);
			if (value instanceof Clob) {
				value = rs.getString(fieldName);
			} else if (value instanceof Blob) {
				value = rs.getBytes(fieldName);
			} else if (value instanceof Date) {
				value = rs.getTimestamp(fieldName);
			} else {
				value = String.valueOf(value);
			}
			entity.set(StrUtil.toCamelCase(fieldName), value);
		}
		return entity;
	}
	private static List<TableIndexColumn> loadTableIndexColumn(String datasourceId,String tableName,String constraintName) throws SQLException {
		List<TableIndexColumn> tableIndexColumns=new ArrayList<>();
		Connection connection=null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
		try {
			Db db=DbProvider.getInstance().getDb(datasourceId);
			connection=db.getConnection();
	        pstmt = connection.prepareCall("{ call showIndex(?,?)}");
	        pstmt.setObject(1, tableName);	
	        pstmt.setObject(2, constraintName);
	        boolean result = pstmt.execute();
	        if (result) {
		        rs = pstmt.getResultSet();
		        ResultSetMetaData metaData = rs.getMetaData();
				while(rs.next())
				{
					Entity entity=rsToEntity(rs, metaData);
					TableIndexColumn tableIndexColumn=BeanUtil.mapToBean(entity, TableIndexColumn.class, false);
					tableIndexColumns.add(tableIndexColumn);
				}
			}
		} catch (Exception e) {
			log.error("",e);
		}finally {
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					log.error("",e);
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					log.error("",e);
				}
			}
			if (connection!=null) {
				try {
					connection.close();
				} catch (SQLException e) {
					log.error("",e);
				}
			}
		}
		return tableIndexColumns;
	}
	
	
	private static List<TableConstraint> loadTableConstraint(String datasourceId) throws SQLException {
		TableDbMapper<TableConstraint> dbMapper=TableDbMapper.getInstance(datasourceId, "information_schema.TABLE_CONSTRAINTS", TableConstraint.class);
		TableConstraint where=new TableConstraint();
		where.setConstraintSchema(DbProvider.getInstance().dbSchema(datasourceId));
		where.setTableSchema(DbProvider.getInstance().dbSchema(datasourceId));
		List<TableConstraint> tableConstraints=dbMapper.find(where,true);
		for (TableConstraint tableConstraint : tableConstraints) {
			tableConstraint.setConstraintType(tableConstraint.getConstraintType().replace(" ", ""));
		}
		return tableConstraints;
	}
	
	private static List<Table> loadTable(String datasourceId) throws SQLException {
		TableDbMapper<Table> dbMapper=TableDbMapper.getInstance(datasourceId, "information_schema.TABLES", Table.class);
		Table where=new Table();
		where.setTableSchema(DbProvider.getInstance().dbSchema(datasourceId));
		List<Table> tables=dbMapper.find(where,true);
		return tables;
	}
}
